#!/bin/bash
#!/bin/bash
DT=$(date -d '-1 days' +%Y-%m-%d)
if  [ $1 ]
then
DT=$1
fi


# 执行    sqoop用户信息增量导入
# 执行   维护设备_账号绑定权重表  脚本
# 执行   维护空设备临时id表     脚本
SQL="
set hive.exec.dynamic.partition.mode = nonstrict ;
with x1 as (select account,
                   app_id,
                   carrier,
                   device_id,
                   device_type,
                   event_id,
                   ip,
                   latitude,
                   longitude,
                   net_type,
                   os_name,
                   os_version,
                   properties,
                   release_channel,
                   resolution,
                   session_id,
                   province,
                   city,
                   district,
                   new_session,
                   ts,
                   dt
            from doe39.pre_app_log_with_areas_sessioned
            where dt = '$DT'
              and account is not null
              and trim(account) != ''),
     x2 as (select uid, account, register_dt
            from doe39.pre_app_log_account),
     x3 as ( -- 没有账号的数据
         select account,
                app_id,
                carrier,
                device_id,
                device_type,
                event_id,
                ip,
                latitude,
                longitude,
                net_type,
                os_name,
                os_version,
                properties,
                release_channel,
                resolution,
                session_id,
                province,
                city,
                district,
                new_session,
                ts,
                dt
         from doe39.pre_app_log_with_areas_sessioned
         where dt = '$DT'
             and account is null
            or trim(account) = ''),
     x4 as ( --设备_账号: 最大权重  关联上账号的uid
         select t2.device_id,
                t2.account,
                x2.uid
         from (select device_id,
                      account
               from (select device_id,
                            account,
                            weight,
                            row_number() over (partition by device_id order by weight desc) as rn
                     from doe39.pre_app_log_dev_acc_bind_weight
                     where dt = '$DT') t1
               where rn = 1) t2
                  join x2 on t2.account = x2.account),
     x5 as (select device_id, tmp_id
            from doe39.pre_app_log_empty_dev_tmp_id)
-- 第一部分 : 有账号的数据

insert  into   table  doe39.dwd_app_log_event_dtl partition (dt)
select x2.uid as
       guid,
       x1.*
from x1
         join x2
              on x1.account = x2.account
union
-- 第二部分
select
    nvl(x4.uid, x5.tmp_id) as guid,
       x3.*
from x3
         left join x4
                   on x3.device_id = x4.device_id
         left join x5
                   on x3.device_id = x5.device_id
"

hive  -e  '$SQL'